package it.unica.informatica.enotes.database;

import android.app.SearchManager;
import android.content.ContentResolver;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;
import android.provider.BaseColumns;
import android.provider.ContactsContract;

import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.HashMap;

/**
 * This class contains all the operations necessary to manipulate database data
 *
 * @author Mirko Marras and Massimiliano Meles
 * @version 1.0
 */
public class DatabaseAdapter {

    /* ********************************************************************************************
     *                                     VARIABLES AND CONSTANTS                                *
     **********************************************************************************************/

   private Context context;
   private SQLiteDatabase database;
   private DatabaseHelper dbHelper;

   //Create a map of all columns possibly requested by SQLiteQueryBuilder
   private static final HashMap<String, String> mColumnMap = buildColumnMap();


   /*Constants  */
   public static final String DATABASE_TABLE_NOTE = "note";
   public static final String DATABASE_TABLE_TAGS = "tag";
   public static final String DATABASE_TABLE_TAGS_NOTES = "tags_notes";
   public static final String FTS_VIRTUAL_TABLE = "FTStags";

   public static final String KEY_NOTE_ID = "id";
   public static final String KEY_NOTE_TITLE = "title";
   public static final String KEY_NOTE_LASTCHANGE = "last_change";
   public static final String KEY_NOTE_EXTRA_DATA = "extra_data";

   public static final String KEY_TAG_ID = "id";
   public static final String KEY_TAG_NAME = "tag_name";

   public static final String KEY_TAGSNOTES_ID_NOTE = "id_note";
   public static final String KEY_TAGSNOTES_ID_TAG = "id_tag";



    /* ********************************************************************************************
     *                       METHODS FOR CREATION,OPENING AND CLOSING OF DB                       *
     **********************************************************************************************/


   public DatabaseAdapter(Context context) {
      this.context = context;
   }

   public DatabaseAdapter open() throws SQLException {
      dbHelper = new DatabaseHelper(context);
      database = dbHelper.getWritableDatabase();
      return this;
   }

   public void close() {
      dbHelper.close();
   }

    /* ********************************************************************************************
     *                       METHODS USED TO CREATE VALUES FOR QUERYS                             *
     **********************************************************************************************/


   /**
    * The method create a map of ColumnName-Value used to insert a note in DB
    *
    * @param title     The title of the note
    * @param extraData All attachment of the note
    * @return the map ColumnName-Value
    */
   private ContentValues createNoteValues(String title, String extraData) {
      ContentValues values = new ContentValues();
      values.put(KEY_NOTE_TITLE, title);
      values.put(KEY_NOTE_EXTRA_DATA, extraData);

      //Used to define the last change time
      DateFormat dateFormat = new SimpleDateFormat("HH:mm:ss dd/MM/yyyy");
      Calendar cal = Calendar.getInstance();
      String lastChange = dateFormat.format(cal.getTime());

      values.put(KEY_NOTE_LASTCHANGE, lastChange);
      return values;
   }

   /**
    * The method create a map of ColumnName-Value used to insert a tag in DB
    *
    * @param tagName The name of the tag
    * @return the map ColumnName-Value
    */
   private ContentValues createTagValues(String tagName) {
      ContentValues values = new ContentValues();
      values.put(KEY_TAG_NAME, tagName);
      return values;
   }


   private ContentValues createSuggestionValue(String tagName) {
      ContentValues values = new ContentValues();
      values.put(DatabaseHelper.KEY_TAG, tagName);

      return values;
   }

   /**
    * The method create a map of ColumnName-Value used to insert link between notes and tags
    *
    * @param idNote ID of the note
    * @param idTag  ID of the tag
    * @return the map ColumnName-Value
    */
   private ContentValues createLinkValues(long idNote, long idTag) {
      ContentValues values = new ContentValues();
      values.put(KEY_TAGSNOTES_ID_NOTE, idNote);
      values.put(KEY_TAGSNOTES_ID_TAG, idTag);
      return values;
   }
    /* ********************************************************************************************
     *                       METHODS USED TO CREATE OPERATION ON DB                               *
     **********************************************************************************************/

   /**
    * The method insert a note in the DB
    *
    * @param title     The title of the note
    * @param extraData All attachment of the note
    * @return rowID of inserted note
    */
   public long createNote(String title, String extraData) {
      ContentValues initialValues = createNoteValues(title, extraData);
      return database.insertOrThrow(DATABASE_TABLE_NOTE, null, initialValues);
   }

   /**
    * The method insert a tag in the DB (inserted both in virtual suggestion table and tag table)
    *
    * @param tagName The name of the tag
    * @return rowID of inserted tag
    */
   public long createTag(String tagName) {
      ContentValues initialValuesSuggestion = createSuggestionValue(tagName);
      database.insertOrThrow(FTS_VIRTUAL_TABLE, null, initialValuesSuggestion);

      ContentValues initialValues = createTagValues(tagName);
      return database.insertOrThrow(DATABASE_TABLE_TAGS, null, initialValues);
   }

   /**
    * The method insert an association between tag and note in the DB
    *
    * @param idNote ID of the note
    * @param idTag  ID of the tag
    * @return rowID of inserted tag
    */
   public long createLink(long idNote, long idTag) {
      ContentValues initialValues = createLinkValues(idNote, idTag);
      return database.insertOrThrow(DATABASE_TABLE_TAGS_NOTES, null, initialValues);
   }

    /* ********************************************************************************************
     *                       METHODS USED TO DELETE OPERATION ON DB                               *
     **********************************************************************************************/

   /**
    * The method delete a note from the DB
    *
    * @param id id of the note to eliminate
    * @return true if operation gone well, false otherwise
    */
   public boolean deleteNote(long id) {
      return database.delete(DATABASE_TABLE_NOTE, KEY_NOTE_ID + "=" + id, null) > 0;
   }

   /**
    * The method delete a tag from the DB
    *
    * @param id id of the tag to eliminate
    * @return true if operation gone well, false otherwise
    */
   public boolean deleteTag(long id) {

      //To preserve order of tags between tag table and suggestion table, it's dropped all suggestion table, cancelled the selected tag,
      // and insert again all remaining tags on suggestion table
      database.execSQL("DELETE FROM " + FTS_VIRTUAL_TABLE);

      int result = database.delete(DATABASE_TABLE_TAGS, KEY_TAG_ID + "=" + id, null);
      Cursor c = fetchAllTags();

      ContentValues initialValuesSuggestion;
      c.moveToFirst();
      while (!c.isAfterLast()) {
         initialValuesSuggestion = createSuggestionValue(c.getString(0));
         database.insertOrThrow(FTS_VIRTUAL_TABLE, null, initialValuesSuggestion);
         c.moveToNext();
      }

      return result > 0;
   }

   /**
    * Delete from the database the note with the passed ID
    *
    * @param noteId The ID of the note to delete
    * @return True if the note is deleted, false otherwise
    */
   public boolean deleteAllLinks(long noteId) {
      return database.delete(DATABASE_TABLE_TAGS_NOTES, KEY_TAGSNOTES_ID_NOTE + "=" + noteId, null) > 0;
   }

    /* ********************************************************************************************
     *                       METHODS USED TO PERFORM QUERY OPERATION                              *
     **********************************************************************************************/

   /**
    * The method find all notes in DB
    *
    * @return cursor used to parse all result rows.
    */
   public Cursor fetchAllNotes() {
      return database.query(DATABASE_TABLE_NOTE, new String[]{KEY_NOTE_ID, KEY_NOTE_TITLE, KEY_NOTE_LASTCHANGE}, null, null, null, null, null);
   }

   /**
    * The method find all notes in DB
    *
    * @return cursor used to parse all result rows.
    */
   public Cursor fetchAllTags() {
      return database.query(DATABASE_TABLE_TAGS, new String[]{KEY_TAG_NAME}, null, null, null, null, null);
   }

   /**
    * Returns a Cursor over all words that match the given query.
    *
    * @param query   The string to search for.
    * @param columns The columns to include, if null then all are included.
    * @return Cursor over all words that match, or null if none found.
    */
   public Cursor getNoteMatches(String query, String[] columns) {
      String selection;
      String[] selectionArgs;

      //Equal to SELECT <columns> FROM <table> WHERE <KEY_WORD> MATCH 'query*'
      selection = DatabaseHelper.KEY_TAG + " MATCH ?";
      selectionArgs = new String[]{query + "*"};

      return query(selection, selectionArgs, columns);
   }

   /**
    * Returns a Cursor with all note with provided tag.
    *
    * @param string The tag used in selection.
    * @return Cursor with all note with provided tag.
    */
   public Cursor fetchNoteByTag(String string) {
      SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
      queryBuilder.setTables(DATABASE_TABLE_NOTE +
              " join " + DATABASE_TABLE_TAGS_NOTES + " on " +
              DATABASE_TABLE_NOTE + "." + KEY_NOTE_ID + " = " + KEY_TAGSNOTES_ID_NOTE + " join " + DATABASE_TABLE_TAGS + " on " + KEY_TAGSNOTES_ID_TAG + "=" + DATABASE_TABLE_TAGS + "." + KEY_TAG_ID);

      return queryBuilder.query(database, new String[]{DATABASE_TABLE_NOTE + "." + KEY_NOTE_ID, KEY_NOTE_TITLE, KEY_NOTE_LASTCHANGE, KEY_NOTE_EXTRA_DATA}, KEY_TAG_NAME + "= ?", new String[]{string}, null, null, null);
   }

   /**
    * This methods find a tag from a rowId
    *
    * @param rowId   The rowId used to find a tag
    * @param columns Used for projection
    * @return Cursor with the tag
    */
   public Cursor getTag(String rowId, String[] columns) {
      String selection = "rowid = ?";
      String[] selectionArgs = new String[]{rowId};

      return query(selection, selectionArgs, columns);
   }

   /**
    * This methods check if a tag exists into the database
    *
    * @param tagId The tag ID to search
    * @return Cursor with the tag
    */
   public boolean isExistentTag(Long tagId) {
      SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
      queryBuilder.setTables(DATABASE_TABLE_TAGS + " join " + DATABASE_TABLE_TAGS_NOTES + " on " +
              DATABASE_TABLE_TAGS + "." + KEY_TAG_ID + " = " +
              DATABASE_TABLE_TAGS_NOTES + "." + KEY_TAGSNOTES_ID_TAG);

      Cursor c = queryBuilder.query(database,
              new String[]{DATABASE_TABLE_TAGS + "." + KEY_TAG_NAME, DATABASE_TABLE_TAGS + "." + KEY_TAG_ID},
              DATABASE_TABLE_TAGS_NOTES + "." + KEY_TAGSNOTES_ID_TAG + "= ?",
              new String[]{tagId.toString()},
              null,
              null,
              null
      );

      return c.getCount() > 0;
   }

   /**
    * Get a cursor that contains the database row associated to the note with the passed ID if exists
    *
    * @param noteId The ID of the note to retrieve
    * @return A cursor that contains the database row associated to the note with the passed ID
    */
   public Cursor getNoteById(Long noteId) {
      String[] projection = {KEY_NOTE_ID, KEY_NOTE_TITLE, KEY_NOTE_EXTRA_DATA, KEY_NOTE_LASTCHANGE};
      String selection = "id = ?";
      String[] selectionArgs = new String[]{noteId.toString()};

      return database.query(
              DATABASE_TABLE_NOTE,
              projection,
              selection,
              selectionArgs,
              null,
              null,
              null
      );
   }

   /**
    * Get a cursor that contains the tag ID associated to the passed tag name if exists
    *
    * @param tag The tag name to retrieve
    * @return A cursor that contains the tag ID associated to the passed tag name
    */
   public Cursor getTagIdByName(String tag) {
      String[] projection = {KEY_TAG_ID, KEY_TAG_NAME};
      String selection = "tag_name = ?";
      String[] selectionArgs = new String[]{tag};

      return database.query(
              DATABASE_TABLE_TAGS,
              projection,
              selection,
              selectionArgs,
              null,
              null,
              null
      );
   }

   /**
    * Update the contents of the note with the passed ID
    *
    * @param id        The ID of the note to update
    * @param title     The new title for the note
    * @param extraData The JSON string that contains text content and attachments of the note
    * @return True if the update is performed, false otherwise
    */
   public boolean updateNote(long id, String title, String extraData) {
      ContentValues updateValues = createNoteValues(title, extraData);
      return database.update(DATABASE_TABLE_NOTE, updateValues, KEY_NOTE_ID + "=" + id, null) > 0;
   }

   /**
    * Get a cursor that contains all the tags names associated to the note with the passed ID
    *
    * @param noteId The ID of the note to retrieve
    * @return A cursor that contains all the tags names associated to the note with the passed ID
    */
   public Cursor getTagsByNote(Long noteId) {
      SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
      queryBuilder.setTables(DATABASE_TABLE_TAGS + " join " + DATABASE_TABLE_TAGS_NOTES + " on " +
              DATABASE_TABLE_TAGS + "." + KEY_TAG_ID + " = " +
              DATABASE_TABLE_TAGS_NOTES + "." + KEY_TAGSNOTES_ID_TAG);

      return queryBuilder.query(database,
              new String[]{DATABASE_TABLE_TAGS + "." + KEY_TAG_NAME, DATABASE_TABLE_TAGS + "." + KEY_TAG_ID},
              DATABASE_TABLE_TAGS_NOTES + "." + KEY_TAGSNOTES_ID_NOTE + "= ?",
              new String[]{noteId.toString()},
              null,
              null,
              null);
   }

   /**
    * The method is used to retrieve all tag for SearchView suggestions
    *
    * @param selection     A selection criteria to apply when filtering rows. If null then all rows are included.
    * @param selectionArgs If in selection there is ?s , it's replaced by selectionArgs
    * @param columns       columns for projection (not used)
    * @return cursor to fetch all results
    */
   private Cursor query(String selection, String[] selectionArgs, String[] columns) {

      SQLiteQueryBuilder builder = new SQLiteQueryBuilder();
      //The search is performed in the virtual table
      builder.setTables(FTS_VIRTUAL_TABLE);
      //The list of columns for projection is taken from the map created by buildColumnMap
      builder.setProjectionMap(mColumnMap);

      Cursor cursor = builder.query(dbHelper.getReadableDatabase(),
              columns, selection, selectionArgs, null, null, null);

      if (cursor == null)
         return null;
      else if (!cursor.moveToFirst()) {
         cursor.close();
         return null;
      }
      return cursor;
   }


   /**
    * Builds a map for all columns possibly requested. ContentProvider can request
    * columns without the need to know real column names.
    *
    * @return the map with all columns names.
    */
   private static HashMap<String, String> buildColumnMap() {
      HashMap<String, String> map = new HashMap<String, String>();
      map.put(DatabaseHelper.KEY_TAG, DatabaseHelper.KEY_TAG);
      map.put(BaseColumns._ID, "rowid AS " + BaseColumns._ID);
      map.put(SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID, "rowid AS " + SearchManager.SUGGEST_COLUMN_INTENT_DATA_ID);
      return map;
   }

}